﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Collections;
using PTTK19.BUS;
using System.Data.SqlClient;

namespace PTTK19.DB
{
    class DB_SanPham
    {

        public static DataTable LayDanhSachSP()
        {
            DataTable ds = new DataTable();
            DBMS dbms = new DBMS();
            dbms.Connect();
            string sql = "  SELECT sp.MaSP,TenSP,SoLuongTon FROM SanPham sp, Kho k WHERE sp.MaSP = k.MaSP";
            dbms.Connect();
            ds = dbms.ExcuteQueryTable(sql);
            dbms.Disconnect();
            return ds;
        }
        public static DataTable LayThongTinChiTietSanPham()
        {
            DataTable ds = new DataTable();
            DBMS dbms = new DBMS();
            dbms.Connect();
            string sql = "SELECT * FROM SanPham ";
            dbms.Connect();
            ds = dbms.ExcuteQueryTable(sql);
            dbms.Disconnect();
            return ds;
        }
        public static DataTable LayMaLoaiSanPham()
        {
            DataTable ds = new DataTable();
            DBMS dbms = new DBMS();
            dbms.Connect();
            string sql = "SELECT MaLoaiSP, TenLoaiSP FROM LoaiSP";
            dbms.Connect();
            ds = dbms.ExcuteQueryTable(sql);
            dbms.Disconnect();
            return ds;
        }
        public static int ThemSanPham(BUS_SanPham sanpham)
        {
            int i = 0;
            DBMS dbms = new DBMS();
            dbms.Connect();
            try
            {
            string sql = "insert into SanPham values('" + sanpham.TenSP + "','" + sanpham.DonVT + "'," + sanpham.MaLoaiSP + "," + sanpham.MaNguyenLieu + "," + sanpham.TinhTrang + ")";
            i = dbms.ExecuteNoneQuery(sql);
            }
            catch(Exception ex)
            {
                i=-1;
            }
            dbms.Disconnect();
            return i;
        }
        public static int XoaSanPham(int Masp)
        {
            int i = 0;
            DBMS dbms = new DBMS();
            dbms.Connect();
            try
            {
                string sql = "DELETE SanPham WHERE MaSP ="+ Masp ;
                i = dbms.ExecuteNoneQuery(sql);
            }
            catch (Exception ex)
            {
                i = -1;
            }
            dbms.Disconnect();
            return i;
        }
        public static int CapNhatSanPham(BUS_SanPham sanpham)
        {
            int i = 0;
            DBMS dbms = new DBMS();
            dbms.Connect();
            try
            {
                string sql = "UPDATE SanPham SET '" + sanpham.TenSP + "','" + sanpham.DonVT + "'," + sanpham.MaLoaiSP + "," + sanpham.MaNguyenLieu + "," + sanpham.TinhTrang + "WHERE MaSP" + sanpham.MaSP;
                i = dbms.ExecuteNoneQuery(sql);
            }
            catch (Exception ex)
            {
                i = -1;
            }
            dbms.Disconnect();
            return i;
        }
    }
}
